---
title: "03-PostgreSQL 查询进阶"
created: 2026-08-31
tags:
  - 项目筑基
---

# PostgreSQL 查询进阶

> PostgreSQL 系统讲解第三篇：PG 查询能力里"MySQL 学不会或不顺手"的部分——CTE（含递归）、窗口函数、UPSERT。学完这三样，复杂查询的写法会上一个台阶。

## CTE：WITH 子句

```sql
WITH hot_users AS (
    SELECT id, name FROM users WHERE created_at > now() - INTERVAL '30 days'
),
order_stats AS (
    SELECT user_id, count(*) cnt, sum(amount) total
    FROM orders GROUP BY user_id
)
SELECT h.name, o.cnt, o.amount
FROM hot_users h JOIN order_stats o ON o.user_id = h.id;
```

CTE = 给中间结果起名字，**复杂查询从"一层套一层"变成"一步步搭积木"**——可读性比子查询嵌套强一个数量级。

**递归 CTE**：查树/层级结构（组织架构、分类树）的杀手锏：

```sql
WITH RECURSIVE sub_tree AS (
    SELECT id, name, parent_id FROM dept WHERE id = 1      -- 起点
    UNION ALL
    SELECT d.id, d.name, d.parent_id
    FROM dept d JOIN sub_tree s ON d.parent_id = s.id      -- 递归：上一轮结果找孩子
)
SELECT * FROM sub_tree;
```

MySQL 8.0 也有递归 CTE 了，但 PG 里这是"日常工具"级别的存在。

## 窗口函数：不折叠行地做聚合

`GROUP BY` 会合并行，窗口函数**保留每一行**，旁边挂上"组内计算结果"：

```sql
-- 每个订单旁边挂上：它在自己用户订单里的金额排名、全表平均
SELECT id, user_id, amount,
       rank()    OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank_in_user,
       avg(amount) OVER (PARTITION BY user_id)                    AS avg_of_user,
       amount - avg(amount) OVER (PARTITION BY user_id)           AS diff_from_avg
FROM orders;

-- 全局 top5（不用 GROUP BY 丢行）
SELECT name, score, rank() OVER (ORDER BY score DESC) AS rk FROM players LIMIT 5;
```

常用件：`rank / dense_rank / row_number`（并列处理不同）、`lag / lead`（上一行/下一行，算环比利器）、`sum() OVER (ORDER BY ts)`（累计值）。

我的记忆钩子：**"每行都要、又要组内统计"就是窗口函数的信号**——比如"每个部门工资最高的人"（用 GROUP BY 会丢掉其他列）。

## UPSERT：ON CONFLICT

MySQL 的 `INSERT ... ON DUPLICATE KEY UPDATE` 在 PG 里对应：

```sql
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (1, 1, now())
ON CONFLICT (user_id) DO UPDATE
    SET login_count = user_stats.login_count + 1, last_login = EXCLUDED.last_login;

ON CONFLICT (user_id) DO NOTHING;    -- 只是想避免重复插入
```

`EXCLUDED` 引用"试图插入的那行"，冲突处理比 MySQL 语义更明确（指定冲突列）。

## 其他顺手的查询能力

- **`RETURNING`**：INSERT/UPDATE/DELETE 直接返回受影响行——省一次 SELECT（01 篇演示过取自增 id）
- **`ILIKE` / 正则**：`name ILIKE '%zh%'`、`name ~ '^(张|李)'`
- **`DISTINCT ON`**：PG 独有——"每组取第一条"一个子句搞定：

```sql
SELECT DISTINCT ON (user_id) user_id, ts, action
FROM logs ORDER BY user_id, ts DESC;     -- 每个用户最近一条日志
```

- **LATERAL**：子查询引用前面表的列（"每用户取最新 3 单"这类 Top-N per group 的高效写法）

## 全文检索入门

```sql
SELECT to_tsvector('simple', 'Redis 是内存数据库') @@ to_tsquery('simple', 'Redis');
-- 中文要配 zhparser/pg_jieba 扩展做分词（动手时核对）

CREATE INDEX ON docs USING GIN (to_tsvector('simple', content));
```

定位：内置全文检索适合"够用就好"的场景；专业搜索仍上 ES——**但 GIN + tsvector 能顶掉一半的"给内容加个搜索"需求**，不用为小需求再养一个中间件。

---
⬅️ [[02-PostgreSQL 数据类型与表设计|02-PostgreSQL 数据类型与表设计]] 🏠 [[00-数据库|00-数据库]] ➡️ [[04-PostgreSQL 索引与 MVCC|04-PostgreSQL 索引与 MVCC]]